
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/17/2012 21:11:48
-- Generated from EDMX file: C:\Users\mike\documents\visual studio 2010\Projects\MachineQA\MachineQA\QAModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [QADatabase];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_EnergyMachine_Energies]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[EnergyMachine] DROP CONSTRAINT [FK_EnergyMachine_Energies];
GO
IF OBJECT_ID(N'[dbo].[FK_EnergyMachine_Machines]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[EnergyMachine] DROP CONSTRAINT [FK_EnergyMachine_Machines];
GO
IF OBJECT_ID(N'[dbo].[FK_LocationMachine]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Machines] DROP CONSTRAINT [FK_LocationMachine];
GO
IF OBJECT_ID(N'[dbo].[FK_MachineTest]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Tests] DROP CONSTRAINT [FK_MachineTest];
GO
IF OBJECT_ID(N'[dbo].[FK_MeasurementValueCriteria]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ValueCriterias] DROP CONSTRAINT [FK_MeasurementValueCriteria];
GO
IF OBJECT_ID(N'[dbo].[FK_ModalityEnergy]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Energies] DROP CONSTRAINT [FK_ModalityEnergy];
GO
IF OBJECT_ID(N'[dbo].[FK_PassFailCriteriaMeasurement]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PassFailCriterias] DROP CONSTRAINT [FK_PassFailCriteriaMeasurement];
GO
IF OBJECT_ID(N'[dbo].[FK_TestMeasurement]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Measurements] DROP CONSTRAINT [FK_TestMeasurement];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Energies]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Energies];
GO
IF OBJECT_ID(N'[dbo].[EnergyMachine]', 'U') IS NOT NULL
    DROP TABLE [dbo].[EnergyMachine];
GO
IF OBJECT_ID(N'[dbo].[Locations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Locations];
GO
IF OBJECT_ID(N'[dbo].[Machines]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Machines];
GO
IF OBJECT_ID(N'[dbo].[Measurements]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Measurements];
GO
IF OBJECT_ID(N'[dbo].[Modalities]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Modalities];
GO
IF OBJECT_ID(N'[dbo].[PassFailCriterias]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PassFailCriterias];
GO
IF OBJECT_ID(N'[dbo].[Tests]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tests];
GO
IF OBJECT_ID(N'[dbo].[ValueCriterias]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ValueCriterias];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Energies'
CREATE TABLE [dbo].[Energies] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [value] float  NOT NULL,
    [Modality_Id] int  NOT NULL
);
GO

-- Creating table 'Locations'
CREATE TABLE [dbo].[Locations] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Machines'
CREATE TABLE [dbo].[Machines] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Location_Id] int  NOT NULL
);
GO

-- Creating table 'Measurements'
CREATE TABLE [dbo].[Measurements] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Test_Id] int  NOT NULL
);
GO

-- Creating table 'Modalities'
CREATE TABLE [dbo].[Modalities] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'PassFailCriterias'
CREATE TABLE [dbo].[PassFailCriterias] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Value] bit  NOT NULL,
    [Measurements_Id] int  NOT NULL
);
GO

-- Creating table 'Tests'
CREATE TABLE [dbo].[Tests] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Machine_Id] int  NOT NULL
);
GO

-- Creating table 'ValueCriterias'
CREATE TABLE [dbo].[ValueCriterias] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Baseline] nvarchar(max)  NOT NULL,
    [LowerRange] nvarchar(max)  NOT NULL,
    [UpperRange] nvarchar(max)  NOT NULL,
    [Measurement_Id] int  NOT NULL
);
GO

-- Creating table 'EnergyMachine'
CREATE TABLE [dbo].[EnergyMachine] (
    [Energies_Id] int  NOT NULL,
    [Machines_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Energies'
ALTER TABLE [dbo].[Energies]
ADD CONSTRAINT [PK_Energies]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Locations'
ALTER TABLE [dbo].[Locations]
ADD CONSTRAINT [PK_Locations]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Machines'
ALTER TABLE [dbo].[Machines]
ADD CONSTRAINT [PK_Machines]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Measurements'
ALTER TABLE [dbo].[Measurements]
ADD CONSTRAINT [PK_Measurements]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Modalities'
ALTER TABLE [dbo].[Modalities]
ADD CONSTRAINT [PK_Modalities]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PassFailCriterias'
ALTER TABLE [dbo].[PassFailCriterias]
ADD CONSTRAINT [PK_PassFailCriterias]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Tests'
ALTER TABLE [dbo].[Tests]
ADD CONSTRAINT [PK_Tests]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ValueCriterias'
ALTER TABLE [dbo].[ValueCriterias]
ADD CONSTRAINT [PK_ValueCriterias]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Energies_Id], [Machines_Id] in table 'EnergyMachine'
ALTER TABLE [dbo].[EnergyMachine]
ADD CONSTRAINT [PK_EnergyMachine]
    PRIMARY KEY NONCLUSTERED ([Energies_Id], [Machines_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Modality_Id] in table 'Energies'
ALTER TABLE [dbo].[Energies]
ADD CONSTRAINT [FK_ModalityEnergy]
    FOREIGN KEY ([Modality_Id])
    REFERENCES [dbo].[Modalities]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ModalityEnergy'
CREATE INDEX [IX_FK_ModalityEnergy]
ON [dbo].[Energies]
    ([Modality_Id]);
GO

-- Creating foreign key on [Location_Id] in table 'Machines'
ALTER TABLE [dbo].[Machines]
ADD CONSTRAINT [FK_LocationMachine]
    FOREIGN KEY ([Location_Id])
    REFERENCES [dbo].[Locations]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_LocationMachine'
CREATE INDEX [IX_FK_LocationMachine]
ON [dbo].[Machines]
    ([Location_Id]);
GO

-- Creating foreign key on [Machine_Id] in table 'Tests'
ALTER TABLE [dbo].[Tests]
ADD CONSTRAINT [FK_MachineTest]
    FOREIGN KEY ([Machine_Id])
    REFERENCES [dbo].[Machines]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MachineTest'
CREATE INDEX [IX_FK_MachineTest]
ON [dbo].[Tests]
    ([Machine_Id]);
GO

-- Creating foreign key on [Measurement_Id] in table 'ValueCriterias'
ALTER TABLE [dbo].[ValueCriterias]
ADD CONSTRAINT [FK_MeasurementValueCriteria]
    FOREIGN KEY ([Measurement_Id])
    REFERENCES [dbo].[Measurements]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MeasurementValueCriteria'
CREATE INDEX [IX_FK_MeasurementValueCriteria]
ON [dbo].[ValueCriterias]
    ([Measurement_Id]);
GO

-- Creating foreign key on [Measurements_Id] in table 'PassFailCriterias'
ALTER TABLE [dbo].[PassFailCriterias]
ADD CONSTRAINT [FK_PassFailCriteriaMeasurement]
    FOREIGN KEY ([Measurements_Id])
    REFERENCES [dbo].[Measurements]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PassFailCriteriaMeasurement'
CREATE INDEX [IX_FK_PassFailCriteriaMeasurement]
ON [dbo].[PassFailCriterias]
    ([Measurements_Id]);
GO

-- Creating foreign key on [Test_Id] in table 'Measurements'
ALTER TABLE [dbo].[Measurements]
ADD CONSTRAINT [FK_TestMeasurement]
    FOREIGN KEY ([Test_Id])
    REFERENCES [dbo].[Tests]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TestMeasurement'
CREATE INDEX [IX_FK_TestMeasurement]
ON [dbo].[Measurements]
    ([Test_Id]);
GO

-- Creating foreign key on [Energies_Id] in table 'EnergyMachine'
ALTER TABLE [dbo].[EnergyMachine]
ADD CONSTRAINT [FK_EnergyMachine_Energies]
    FOREIGN KEY ([Energies_Id])
    REFERENCES [dbo].[Energies]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Machines_Id] in table 'EnergyMachine'
ALTER TABLE [dbo].[EnergyMachine]
ADD CONSTRAINT [FK_EnergyMachine_Machines]
    FOREIGN KEY ([Machines_Id])
    REFERENCES [dbo].[Machines]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EnergyMachine_Machines'
CREATE INDEX [IX_FK_EnergyMachine_Machines]
ON [dbo].[EnergyMachine]
    ([Machines_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------